CREATE TABLE [dbo].[PackageItem]
(
[PackageKey] [uniqueidentifier] NOT NULL,
[PackageItemKey] [uniqueidentifier] NOT NULL,
[SupplementKey] [uniqueidentifier] NOT NULL,
[UpdatedOn] [datetime] NOT NULL,
[UpdatedByUserKey] [uniqueidentifier] NOT NULL,
[CreatedByUserKey] [uniqueidentifier] NOT NULL,
[CreatedOn] [datetime] NOT NULL,
[MarkedForDeleteOn] [datetime] NULL
) ON [PRIMARY]

GO
CREATE  TRIGGER [dbo].[asi_PackageItem_Insert_Update]
    ON [dbo].[PackageItem]
    FOR INSERT, UPDATE
AS
BEGIN
    UPDATE    pm1
    SET    pm1.Cost =
        (SELECT    COALESCE(Sum(sm2.Cost), 0)
         FROM PackageMain pm2
            INNER JOIN PackageItem pi2 on pm2.PackageKey = pi2.PackageKey
            INNER JOIN SupplementMain sm2 on pi2.SupplementKey = sm2.SupplementKey
         WHERE pm2.PackageKey = pm1.PackageKey)
    FROM PackageMain pm1
        INNER JOIN inserted on pm1.PackageKey = inserted.PackageKey
END

GO
ALTER TABLE [dbo].[PackageItem] ADD CONSTRAINT [PK_PackageItem] PRIMARY KEY CLUSTERED ([PackageItemKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_PackageItem_CreatedByUserKey] ON [dbo].[PackageItem] ([CreatedByUserKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_PackageItem_PackageKey] ON [dbo].[PackageItem] ([PackageKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_PackageItem_SupplementKey] ON [dbo].[PackageItem] ([SupplementKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_PackageItem_UpdatedByUserKey] ON [dbo].[PackageItem] ([UpdatedByUserKey]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PackageItem] ADD CONSTRAINT [FK_PackageItem_PackageMain] FOREIGN KEY ([PackageKey]) REFERENCES [dbo].[PackageMain] ([PackageKey])
GO
ALTER TABLE [dbo].[PackageItem] ADD CONSTRAINT [FK_PackageItem_Supplement] FOREIGN KEY ([SupplementKey]) REFERENCES [dbo].[SupplementMain] ([SupplementKey])
GO
ALTER TABLE [dbo].[PackageItem] ADD CONSTRAINT [FK_PackageItem_UserMain_CreatedBy] FOREIGN KEY ([CreatedByUserKey]) REFERENCES [dbo].[UserMain] ([UserKey])
GO
ALTER TABLE [dbo].[PackageItem] ADD CONSTRAINT [FK_PackageItem_UserMain_UpdatedBy] FOREIGN KEY ([UpdatedByUserKey]) REFERENCES [dbo].[UserMain] ([UserKey])
GO